<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1"><title id="ep20941">TRUNCATE</title><body><p id="sql_command_desc">Empties a table of all rows.</p>
    <note type="note">Greenplum Database does not enforce referential integrity syntax (foreign key
      constraints). As of version 6.12 <codeph>TRUNCATE</codeph> truncates a table that is
      referenced in a foreign key constraint even if the <codeph>CASCADE</codeph> option is
      omitted.</note><section id="section2"><title>Synopsis</title><codeblock id="sql_command_synopsis">TRUNCATE [TABLE] [ONLY] <varname>name</varname> [ * ] [, ...] 
    [ RESTART IDENTITY | CONTINUE IDENTITY ] [CASCADE | RESTRICT]</codeblock></section>
    <section id="section3"><title>Description</title><p><codeph>TRUNCATE</codeph> quickly removes
        all rows from a table or set of tables. It has the same effect as an unqualified
            <codeph><xref href="DELETE.xml#topic1" type="topic" format="dita"/></codeph> on each
        table, but since it does not actually scan the tables it is faster. This is most useful on
        large tables.</p>
      <p>You must have the <codeph>TRUNCATE</codeph> privilege on the table to truncate table
        rows.</p><p><codeph>TRUNCATE</codeph> acquires an access exclusive lock on the tables it
        operates on, which blocks all other concurrent operations on the table. When <codeph>RESTART
          IDENTITY</codeph> is specified, any sequences that are to be restarted are likewise locked
        exclusively. If concurrent access to a table is required, then the <codeph>DELETE</codeph>
          command should be used instead. </p></section><section id="section4"><title>Parameters</title><parml><plentry><pt><varname>name</varname></pt><pd>The name (optionally schema-qualified) of a table to truncate. If <codeph>ONLY</codeph> is
            specified before the table name, only that table is truncated. If <codeph>ONLY</codeph>
            is not specified, the table and all its descendant tables (if any) are truncated.
            Optionally, <codeph>*</codeph> can be specified after the table name to explicitly
            indicate that descendant tables are included.</pd></plentry><plentry><pt>CASCADE</pt><pd>Because this key word applies to foreign key references (which are not supported in Greenplum
            Database) it has no effect.</pd></plentry>
        <plentry>
          <pt>RESTART IDENTITY</pt>
          <pd>Automatically restart sequences owned by columns of the truncated table(s).</pd>
        </plentry>
        <plentry>
          <pt>CONTINUE IDENTITY</pt>
          <pd>Do not change the values of sequences. This is the default.</pd>
        </plentry><plentry><pt>RESTRICT</pt><pd>Because this key word applies to foreign key references (which are not supported in Greenplum
            Database) it has no effect.</pd></plentry></parml></section><section id="section5"><title>Notes</title><p><codeph>TRUNCATE</codeph> will not run any user-defined <codeph>ON
              DELETE</codeph> triggers that might exist for the tables.</p><p><codeph>TRUNCATE</codeph> will not truncate any tables that inherit
from the named table. Only the named table is truncated, not its child
tables.</p><p><codeph>TRUNCATE</codeph> will not truncate any sub-tables of a partitioned
table. If you specify a sub-table of a partitioned table, <codeph>TRUNCATE</codeph>
  will not remove rows from the sub-table and its child tables.</p>
              <p> <codeph>TRUNCATE</codeph> is not MVCC-safe. After truncation, the table will
                appear empty to concurrent transactions, if they are using a snapshot
                taken before the truncation occurred.</p>
      <p><codeph>TRUNCATE</codeph> is transaction-safe with respect to the data in the tables: the
        truncation will be safely rolled back if the surrounding transaction does not commit.</p>
      <p><codeph>TRUNCATE</codeph> acquires an <codeph>ACCESS EXCLUSIVE</codeph> lock on each table
        it operates on, which blocks all other concurrent operations on the table. If concurrent
        access to a table is required, then the <codeph>DELETE</codeph> command should be used
        instead. </p>
      <p>When <codeph>RESTART IDENTITY</codeph> is specified, the implied <codeph>ALTER SEQUENCE
          RESTART</codeph> operations are also done transactionally; that is, they will be rolled
        back if the surrounding transaction does not commit. This is unlike the normal behavior of
          <codeph>ALTER SEQUENCE RESTART</codeph>. Be aware that if any additional sequence
        operations are done on the restarted sequences before the transaction rolls back, the
        effects of these operations on the sequences will be rolled back, but not their effects on
          <codeph>currval()</codeph>; that is, after the transaction <codeph>currval()</codeph> will
        continue to reflect the last sequence value obtained inside the failed transaction, even
        though the sequence itself may no longer be consistent with that. This is similar to the
        usual behavior of <codeph>currval()</codeph> after a failed transaction.</p>
</section><section id="section6"><title>Examples</title><p>Empty the tables <codeph>films</codeph> and <codeph>distributors</codeph>:</p><codeblock>TRUNCATE films, distributors;</codeblock>
      <p>The same, and also reset any associated sequence generators:</p>
  <codeblock>TRUNCATE films, distributors RESTART IDENTITY;</codeblock></section><section id="section7"><title>Compatibility</title>
      <p>The SQL:2008 standard includes a <codeph>TRUNCATE</codeph> command with the syntax
          <codeph>TRUNCATE TABLE <varname>tablename</varname></codeph>. The clauses <codeph>CONTINUE
          IDENTITY</codeph>/<codeph>RESTART IDENTITY</codeph> also appear in that standard, but have
        slightly different though related meanings. Some of the concurrency behavior of this command
        is left implementation-defined by the standard, so the above notes should be considered and
        compared with other implementations if necessary. </p></section><section id="section8"><title>See Also</title><p><codeph><xref href="DELETE.xml#topic1" type="topic" format="dita"/></codeph>, <codeph><xref
            href="DROP_TABLE.xml#topic1" type="topic" format="dita"/></codeph></p></section></body></topic>
